﻿# -*- coding: utf-8 -*-

"""
PyoCDVD (http://code.google.com/p/pyocdvd/)
Media Organizer (CD, DVD, etc)

Developers:
    Bruno Pitteli Gonçalves <scorninpc(*)gmail(*)com>
    Paul Eipper <lkraider(*)gmail(*)com>

Licence: GNU GENERAL PUBLIC LICENSE v3

"""

import os.path
import database_sql

class PyoCDVD_Database(database_sql.Database_SQL):
    '''PyoCDVD database API class.'''

    def __init__(self, database_file='pyocdvd.db', sql_script='database/docs/pyocdvd.sql'):
        if os.path.isfile(database_file):
            database_sql.Database_SQL.__init__(self, database_file)
        else:
            database_sql.Database_SQL.__init__(self, database_file)
            self.create_database(sql_script)
        
    def insert_disc(self, data={}):
        '''Insert the disc data and return the ID used for the disc.'''
        data['id_disco'] = self.next_disc_id()
        self.insert('discos', data)
        return data['id_disco']
            
    def insert_file(self, id_disco, name, metadata={}):
        '''Insert the file data and return the ID used for the file.
        
        id_disco is the ID of the disc the file pertains to;
        name is the filename;
        metadata is a dict with the file's metadata (optional).
        
        '''
        data = {'id_disco': id_disco, 'nome': name}
        data['id_arquivo'] = self.next_file_id()
        self.insert('arquivos', data)
        if metadata:
            self.insert_metadata(data['id_arquivo'], metadata)
        return data['id_arquivo']
        
    def insert_metadata(self, id_arquivo, metadata={}):
        '''Insert the file metadata.
        
        id_arquivo is the ID of the file the metadata pertains to;
        metadata dict contains the metadata key:value pairs.
        
        '''
        data = {'id_arquivo': id_arquivo}
        for key, value in metadata.items():
            data['id_metadata'] = self.next_meta_id()
            data['campo'] = key
            data['dados'] = value
            self.insert('metadata', data)

    def insert_label(self, name):
        '''Insert a new label and return it's ID, while checking if it's not repeated.'''
        data = {'id_label': self.next_label_id(), 'name': name}
        existing_label = self.select_label_name(name)
        if existing_label:
            return existing_label[0][0]
        self.insert('labels', data)
        return data['id_label']

    def insert_disc_label(self, id_disco, id_label):
        '''Insert a disc <-> label relationship.'''
        data = {'id_disco': id_disco, 'id_label': id_label}
        self.insert('discos_com_labels', data)
        
    def insert_disc_with_labels(self, disc_data={}, labels=()):
        '''Insert a new disc and associated labels directly.'''
        disc_id = self.insert_disc(disc_data)
        if type(labels) != tuple:
            labels = (labels,)
        for label in labels:
            label_id = self.insert_label(label)
            self.insert_disc_label(disc_id, label_id)
        return disc_id

    def next_disc_id(self):
        '''Return the next usable disc id.'''
        return self.next_id('discos', 'id_disco')
        
    def next_file_id(self):
        '''Return the next usable file id.'''
        return self.next_id('arquivos', 'id_arquivo')
        
    def next_meta_id(self):
        '''Return the next usable metadata id.'''
        return self.next_id('metadata', 'id_metadata')
        
    def next_label_id(self):
        '''Return the next usable label id.'''
        return self.next_id('labels', 'id_label')

    def select_disc(self, id_disco):
        '''Return the data associated to a disc id'''
        return self.select_where('discos', '*', {'id_disco': id_disco}).fetchone()

    def select_all_discs(self):
        '''Return all the discs table data'''
        return self.select('discos', '*').fetchall()

    def select_disc_files(self, id_disco):
        '''Return the list of files in a disc.'''
        return self.select_where('arquivos', '*', {'id_disco': id_disco}).fetchall()

    def select_file_metadata(self, id_arquivo):
        '''Return the metadata related to a file ID.'''
        return self.select_where('metadata', '*', {'id_arquivo': id_arquivo}).fetchall()
        
    def select_label_name(self, name):
        '''Return the label matching a specific name.'''
        return self.select_where('labels', '*', {'name': name}).fetchall()
        
    def select_disc_labels(self, id_disco):
        '''Return the labels applied to a disc.'''
        return self.select_where('labels, discos_com_labels', 'labels.*',
                                  {'labels.id_label': ['discos_com_labels.id_label'], 'id_disco': id_disco}).fetchall()
                                  
    def select_discs_with_label(self, label):
        '''Return the discs that have a certain label.'''
        id_label = self.select_label_name(label)[0][0]
        return self.select_where('discos, discos_com_labels', 'discos.*',
                                  {'discos.id_disco': ['discos_com_labels.id_disco'], 'id_label': id_label}).fetchall()
                                  
    def select_discs_with_file(self, filename):
        '''Return dictionary of discs that contain a certain file
        and the filenames found matching. The format is as follows:
        
        {(disc1_data): [(file1_data), (file2_data)]}
            
        '''
        select = self.select_where('discos, arquivos',
                                   'discos.*, arquivos.id_arquivo, arquivos.nome',
                                   {'discos.id_disco': ['arquivos.id_disco'], 'arquivos.nome': filename},
                                   compare_like=True)
        result = {}
        for row in select:
            #Group disc data:
            disc = []
            for d in range(4):
                disc.append(row[d])
            disc = tuple(disc)
            #File data:
            file = (row[4], row[5])
            #Disc data as dictionary keys:
            if not result.has_key(disc):
                result[disc] = []
            #Append file to disc:
            result[disc].append(file)
        return result